# -*- coding: utf-8 -*-
"""
Excel数据读取工具
用于从Excel文件中读取测试用例数据
"""

import pandas as pd
import logging
from pathlib import Path
from typing import List, Dict, Any, Optional


class ExcelTestDataReader:
    """Excel测试数据读取器"""
    
    def __init__(self, excel_file_path: str):
        """
        初始化Excel数据读取器
        
        Args:
            excel_file_path: Excel文件路径
        """
        self.excel_file_path = Path(excel_file_path)
        self.logger = logging.getLogger(__name__)
        
        if not self.excel_file_path.exists():
            raise FileNotFoundError(f"Excel文件不存在: {excel_file_path}")
    
    def read_test_cases(self, sheet_name: str = 'Sheet1') -> List[Dict[str, Any]]:
        """
        读取测试用例数据
        
        Args:
            sheet_name: 工作表名称
            
        Returns:
            测试用例数据列表
        """
        try:
            df = pd.read_excel(self.excel_file_path, sheet_name=sheet_name)
            
            # 将DataFrame转换为字典列表
            test_cases = df.to_dict('records')
            
            # 清理数据，处理NaN值
            cleaned_cases = []
            for case in test_cases:
                cleaned_case = {}
                for key, value in case.items():
                    # 处理NaN值
                    if pd.isna(value):
                        cleaned_case[key] = None if key in ['name', 'email'] else ''
                    else:
                        cleaned_case[key] = value
                cleaned_cases.append(cleaned_case)
            
            self.logger.info(f"从工作表 '{sheet_name}' 读取了 {len(cleaned_cases)} 个测试用例")
            return cleaned_cases
            
        except Exception as e:
            self.logger.error(f"读取Excel文件失败: {e}")
            raise
    
    def read_test_cases_by_method(self, api_method: str, sheet_name: str = 'Sheet1') -> List[Dict[str, Any]]:
        """
        根据API方法筛选测试用例
        
        Args:
            api_method: API方法 (GET, POST, PUT, DELETE)
            sheet_name: 工作表名称
            
        Returns:
            筛选后的测试用例数据列表
        """
        all_cases = self.read_test_cases(sheet_name)
        filtered_cases = [case for case in all_cases if case.get('api_method') == api_method]
        
        self.logger.info(f"筛选出 {len(filtered_cases)} 个 {api_method} 方法的测试用例")
        return filtered_cases
    
    def read_test_cases_by_path(self, api_path: str, sheet_name: str = 'Sheet1') -> List[Dict[str, Any]]:
        """
        根据API路径筛选测试用例
        
        Args:
            api_path: API路径
            sheet_name: 工作表名称
            
        Returns:
            筛选后的测试用例数据列表
        """
        all_cases = self.read_test_cases(sheet_name)
        filtered_cases = [case for case in all_cases if case.get('api_path') == api_path]
        
        self.logger.info(f"筛选出 {len(filtered_cases)} 个路径为 '{api_path}' 的测试用例")
        return filtered_cases
    
    def read_test_cases_by_result(self, expected_result: str, sheet_name: str = 'Sheet1') -> List[Dict[str, Any]]:
        """
        根据预期结果筛选测试用例
        
        Args:
            expected_result: 预期结果 (success, validation_error, not_found等)
            sheet_name: 工作表名称
            
        Returns:
            筛选后的测试用例数据列表
        """
        all_cases = self.read_test_cases(sheet_name)
        filtered_cases = [case for case in all_cases if case.get('expected_result') == expected_result]
        
        self.logger.info(f"筛选出 {len(filtered_cases)} 个预期结果为 '{expected_result}' 的测试用例")
        return filtered_cases
    
    def get_sheet_names(self) -> List[str]:
        """
        获取Excel文件中所有工作表名称
        
        Returns:
            工作表名称列表
        """
        try:
            excel_file = pd.ExcelFile(self.excel_file_path)
            return excel_file.sheet_names
        except Exception as e:
            self.logger.error(f"获取工作表名称失败: {e}")
            raise
    
    def build_request_data(self, test_case: Dict[str, Any]) -> Dict[str, Any]:
        """
        根据测试用例构建请求数据
        
        Args:
            test_case: 测试用例数据
            
        Returns:
            请求数据字典
        """
        request_data = {}
        
        # 构建用户数据
        if test_case.get('name'):
            request_data['name'] = test_case['name']
        if test_case.get('email'):
            request_data['email'] = test_case['email']
        if test_case.get('age') and pd.notna(test_case['age']):
            request_data['age'] = int(test_case['age'])
        
        return request_data
    
    def validate_test_case(self, test_case: Dict[str, Any]) -> bool:
        """
        验证测试用例数据的完整性
        
        Args:
            test_case: 测试用例数据
            
        Returns:
            验证结果
        """
        required_fields = ['test_case_id', 'test_name', 'api_method', 'api_path', 'expected_status_code']
        
        for field in required_fields:
            if field not in test_case or pd.isna(test_case[field]):
                self.logger.warning(f"测试用例缺少必填字段: {field}")
                return False
        
        return True


def get_excel_test_data_reader() -> ExcelTestDataReader:
    """
    获取Excel测试数据读取器的单例实例
    
    Returns:
        ExcelTestDataReader实例
    """
    # 获取Excel文件路径
    current_dir = Path(__file__).parent.parent
    data_dir = current_dir / 'data'
    
    # 查找最新的Excel文件（带时间戳的）
    excel_files = list(data_dir.glob('test_cases_*.xlsx'))
    
    if excel_files:
        # 按文件名排序，获取最新的文件
        excel_file_path = sorted(excel_files, key=lambda x: x.name)[-1]
    else:
        # 如果没有找到带时间戳的文件，使用默认文件名
        excel_file_path = data_dir / 'test_cases.xlsx'
    
    return ExcelTestDataReader(str(excel_file_path))


if __name__ == '__main__':
    # 测试Excel数据读取功能
    reader = get_excel_test_data_reader()
    
    print("工作表列表:")
    for sheet in reader.get_sheet_names():
        print(f"  - {sheet}")
    
    print("\n所有测试用例:")
    all_cases = reader.read_test_cases()
    for case in all_cases[:3]:  # 只显示前3个
        print(f"  {case['test_case_id']}: {case['test_name']}")
    
    print("\nPOST方法测试用例:")
    post_cases = reader.read_test_cases_by_method('POST')
    for case in post_cases:
        print(f"  {case['test_case_id']}: {case['test_name']}")